/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Pengadaan;
import model.Penjualan;
import model.Produksi;

/**
 *
 * @author elfatah
 */
public class DBKeuangan extends DBClass {

    public List getProduksiDetails(String tgl1, String tgl2) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT produksi.PRODUKSI_ID, produksi.TANGGAL, barang.KODE_BARANG, produksi.JUMLAH_PRODUKSI, produksi.BIAYA FROM produksi, barang where produksi.ID_BARANG = barang.BARANG_ID and produksi.TANGGAL between ? and ?");
        pstmt.setString(1, tgl1);
        pstmt.setString(2, tgl2);
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();

        while (rs.next()) {
            Produksi ubean = new Produksi();
            ubean.setProduksiId(rs.getString(1));
            ubean.setTgl(rs.getString(2));
            ubean.setKodeBarang(rs.getString(3));
            ubean.setJumlahPrduksi(rs.getString(4));
            ubean.setBiaya(rs.getString(5));

            list.add(ubean);

        }
        return list;
    }

    public List getPengadaanDetails(String tgl1, String tgl2) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT peng.PENGADAAN_ID, peg.USERNAME, sup.NAMA, bar.KODE_BARANG, peng.JUMLAH_BARANG, peng.TANGGAL, peng.HARGA_SATUAN, peng.JUMLAH_HARGA FROM `pengadaan` as peng, supplyer as sup, pegawai as peg, barang as bar WHERE peng.ID_BARANG = bar.BARANG_ID and peg.PEGAWAI_ID = peng.ID_PEGAWAI and peng.ID_SUPPLYER = sup.SUPPLYER_ID and peng.TANGGAL between ? and ?");
        pstmt.setString(1, tgl1);
        pstmt.setString(2, tgl2);
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Pengadaan ubean = new Pengadaan();
            ubean.setPengadaanId(Integer.parseInt(rs.getString(1)));
            ubean.setNamaPegawai(rs.getString(2));
            ubean.setNamaSupplyer(rs.getString(3));
            ubean.setNamaBarang(rs.getString(4));
            ubean.setJumlahBarang(Integer.parseInt(rs.getString(5)));
            ubean.setTgl(rs.getString(6));
            ubean.setHargaSatuan(Integer.parseInt(rs.getString(7)));
            ubean.setJumlahHarga(rs.getString(8));

            list.add(ubean);

        }
        return list;
    }

    public List getPenjualanDetails(String tgl1, String tgl2) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT pen.PENJUALAN_ID, peg.USERNAME, bar.KODE_BARANG, pen.JUMLAH_BARANG, pen.JUMLAH_HARGA, pen.TANGGAL  FROM penjualan as pen,pegawai as peg,barang as bar WHERE peg.PEGAWAI_ID = pen.ID_PEGAWAI and bar.BARANG_ID = pen.ID_BARANG and pen.TANGGAL between ? and ?");
        pstmt.setString(1, tgl1);
        pstmt.setString(2, tgl2);
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Penjualan ubean = new Penjualan();
            ubean.setPenjualanId(rs.getString(1));
            ubean.setUsername(rs.getString(2));
            ubean.setKode_barang(rs.getString(3));
            ubean.setJumlahBarang(rs.getString(4));
            ubean.setJumlahHarga(rs.getString(5));
            ubean.setTgl(rs.getString(6));

            list.add(ubean);

        }
        return list;
    }
}
